postgresql 索引之 gin
os: ubuntu 16.04
postgresql: 9.6.8
ip 规划
192.168.56.102 node2 postgresql
help create index
postgres=# \h create index
Command: CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]
[ USING method ]
method
要使用的索引方法的名称。可以选择 btree、hash、 gist、spgist、 gin以及brin。 默认方法是btree。
gin
gin 意思是通用倒排索引。
gin 被设计为处理被索引项为组合值的情况，并且这种索引所处理的查询需要搜索出现在组合项中的元素值。例如，项可以是文档，并且查询可以是搜索包含指定词的文档。
我们使用词项来表示要被索引的一个组合值，并且用词键来表示一个元素值。gin 总是存储和搜索键，而不是项值本身。
gin 索引是"倒排索引"，它适合于包含多个组成值的数据值，例如数组。
倒排索引中为每一个组成值都包含一个单独的项，它可以高效地处理测试指定组成值是否存在的查询。
简单的说就是 gin 索引接口常被用于多值列的检索，例如全文检索类型、数组类型。
postgres=# drop table tmp_t0;
DROP TABLE
postgres=# create table tmp_t0(c0 tsvector,c1 varchar(100));
CREATE TABLE
postgres=# insert into tmp_t0(c0,c1) select to_tsvector((select string_agg(p0,' ') from regexp_split_to_table(md5(id::varchar),'') as p0)),md5((id)::varchar) from generate_series(1,100000) as id;
INSERT 0 100000
postgres=# \x
Expanded display is on.
postgres=# select * from tmp_t0 limit 2;
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------
c0 | '0':10,17,22 '2':6,13,16 '3':7,14 '4':2,5,30 '5':21,28 '6':25 '7':27 '8':8,15,29 '9':12,23,31 'b':11,32 'c':1,3,19,20 'd':18 'f':26
c1 | c4ca4238a0b923820dcc509a6f75849b
-[ RECORD 2 ]----------------------------------------------------------------------------------------------------------------------------------
c0 | '0':19 '1':3,27 '2':6,13,31 '3':16 '4':11,28 '6':15,17,20,30 '7':5,21 '8':2,7,23,29 '9':9,24 'c':1,12,25,26,32 'd':8,10 'e':4 'f':14,18,22
c1 | c81e728d9d4c2f636f067f89cc14862c
postgres=# \x
Expanded display is off.
postgres=# create index idx_tmp_t0_1 on tmp_t0 using gin (c0);
postgres=# \d+ tmp_t0
Table "public.tmp_t0"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------------------------+-----------+----------+--------------+-------------
c0 | tsvector | | extended | |
c1 | character varying(100) | | extended | |
Indexes:
"idx_tmp_t0_1" gin (c0)
gin 索引与这两个参数有关
postgres=# \x
postgres=# select * from pg_settings where name like '%gin%';
-[ RECORD 1 ]---+---------------------------------------------------------------------------
name | gin_fuzzy_search_limit
setting | 0
unit |
category | Client Connection Defaults / Other Defaults
short_desc | Sets the maximum allowed result for exact search by GIN.
extra_desc |
context | user
vartype | integer
source | default
min_val | 0
max_val | 2147483647
enumvals |
boot_val | 0
reset_val | 0
sourcefile |
sourceline |
pending_restart | f
-[ RECORD 2 ]---+---------------------------------------------------------------------------
name | gin_pending_list_limit
setting | 4096
unit | kB
category | Client Connection Defaults / Statement Behavior
short_desc | Sets the maximum size of the pending list for GIN index.
extra_desc |
context | user
vartype | integer
source | default
min_val | 64
max_val | 2147483647
enumvals |
boot_val | 4096
reset_val | 4096
sourcefile |
sourceline |
pending_restart | f
社区版本中个人的力量
postgresql 中的 gin 实现主要由 Teodor Sigaev 和 Oleg Bartunov 维护。在他们的网站(http://www.sai.msu.su/~megera/wiki/Gin)上有更多关于 gin 的信息。
在 postgresql 10 中，gin 在多并发的压力下性能有了很高的提升。
参考：
http://postgres.cn/docs/9.6/indexes-types.html
http://postgres.cn/docs/9.6/sql-createindex.html
http://postgres.cn/docs/9.6/functions-array.html
http://postgres.cn/docs/9.6/gin.html
